package com.fr.data;

import com.fr.base.Parameter;
import com.fr.general.data.TableDataException;
import com.fr.stable.StringUtils;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * @author liumingchun  17:45 2018-08-20
 */
public class PaymentReportByYearAndCity extends AbstractTableData {
    /**
     * 字段名枚举YEAR
     */
    enum FIELD_NAME {
        YEAR, MONTH, PAYMENT_TYPE_NAME, SALES_AMOUNT, LAST_MONTH_SALES_AMOUNT
    }

    /**
     * 列名数组，保存程序数据集所有列名
     */
    private String[] columNames;
    /**
     * 保存查询得到列值
     */
    private LinkedHashSet<LinkedHashMap<String, Object>> rowData;

    /**
     * 构造函数，定义表结构，该表有10个数据列，列名为column#0，column#1，。。。。。。column#9
     */
    public PaymentReportByYearAndCity() {
        // 定义tableName参数
//        setDefaultParameters(new Parameter[]{new Parameter("tableName")});
        // 定义程序数据集列名
        this.parameters = new Parameter[]{new Parameter("date"), new Parameter("city")};
        // 填充字段名
        columNames = new String[FIELD_NAME.values().length];
        int i = 0;
        for (FIELD_NAME fieldName : FIELD_NAME.values()) {
            columNames[i] = fieldName.toString();
            i++;
        }
    }

    @Override
    public int getColumnCount() throws TableDataException {
        return columNames.length;
    }

    @Override
    public String getColumnName(int columnIndex) throws TableDataException {
        return columNames[columnIndex];
    }

    @Override
    public int getRowCount() throws TableDataException {
        queryData();
        return rowData.size();
    }

    @Override
    public Object getValueAt(int rowIndex, int columnIndex) {
        queryData();
        int tempRowIndex = 0;
        for (LinkedHashMap<String, Object> row : rowData) {
            if (tempRowIndex == rowIndex) {
                return row.get(columNames[columnIndex]);
            }
            tempRowIndex += 1;
        }
        return null;
    }

    /**
     * 查询数据
     */
    public void queryData() {
        // 确保只被执行一次
        if (rowData != null) {
            return;
        }
        // 保存得到的数据库表名
        String date = parameters[0].getValue().toString();
        String city = parameters[1].getValue().toString();
//        String date = "FRI Jun 01 2018 00:00:00 GMT+0800";
//        String city = "";
        long start = System.currentTimeMillis();
        // 保存得到的结果集
        rowData = new LinkedHashSet<LinkedHashMap<String, Object>>();
        // 下面开始建立数据库连接，按照刚才的SQL语句进行查询
        Connection conn = ConnectUtil.getConnection();
        try {
            Date date1 = new Date(date);
            String yearCondition = new SimpleDateFormat("yyyy").format(date1);
            StringBuffer condition = new StringBuffer();
            if (StringUtils.isNotEmpty(yearCondition)) {
                condition.append("  AND v.YEAR = '" + yearCondition + "'");
            }
            if (StringUtils.isNotEmpty(city)) {
                condition.append("  AND v.CITY = '" + city + "'");
            }

            Statement stmt = conn.createStatement();
            //查询经销商Id
            String partyIdSql = "SELECT s.party_id FROM zdev_store_info s  GROUP BY s.party_id ";
            List partyList = new ArrayList();
            ResultSet partyIdReportRs = stmt.executeQuery(partyIdSql.toString());
            LinkedHashMap<String, Object> partyMap;
            while (partyIdReportRs.next()) {
                partyMap = new LinkedHashMap<String, Object>();
                partyMap.put("partyId", partyIdReportRs.getString(1));
                partyList.add(partyMap);
            }
            partyIdReportRs.close();

            List reportList = new ArrayList();
            for (int i = 0; i < partyList.size(); i++) {
                LinkedHashMap<String, Object> map = (LinkedHashMap<String, Object>) partyList.get(i);
                String partyId = String.valueOf(map.get("partyId"));
                //判断表是否存在
                String tableExist = "select count(1) from user_tables where table_name = 'ZDEV_SALES_DTL" + partyId + "_V2'";
                ResultSet tableExistResultSet = stmt.executeQuery(tableExist);
                String tableExistFlag = "";
                while (tableExistResultSet.next()) {
                    tableExistFlag = tableExistResultSet.getString(1);
                }
                if ("1".equals(tableExistFlag)) {
                    StringBuffer paymentReportSql = new StringBuffer();
                    paymentReportSql.append("SELECT V.YEAR, V.QUANTITY, CASE WHEN t1.payment_type_name IS NULL THEN t.description  ELSE t1.payment_type_name  END payment_type_name ,  SUM(V.payment_amount) QUANTITY,");
                    paymentReportSql.append("  NULLIF(LAG(SUM(V.payment_amount), 1, 0)  OVER(PARTITION BY V.YEAR,V.payment_method_type_id,v.dim_id ORDER BY V.QUANTITY),0) AS LAST_MONTH_SALES_AMOUNT");
                    paymentReportSql.append(" FROM ZDEV_PM_DTL" + partyId + "_V2 V");
                    paymentReportSql.append(" INNER JOIN payment_method_type t ON t.payment_method_type_id = v.payment_method_type_id");
                    paymentReportSql.append(" LEFT JOIN miya_payment_type t1 ON t1.payment_type_id = v.dim_id");
                    paymentReportSql.append(" WHERE V.DIM_TYPE = '00' ");
                    paymentReportSql.append(condition);
                    paymentReportSql.append(" GROUP BY V.YEAR, V.QUANTITY, V.payment_method_type_id,    v.dim_id,t.description,t1.payment_type_name");
                    paymentReportSql.append(" ORDER BY v.YEAR,v.QUANTITY,t1.payment_type_name");
                    ResultSet hourReportRs = stmt.executeQuery(paymentReportSql.toString());
                    while (hourReportRs.next()) {
                        LinkedHashMap<String, Object> info = new LinkedHashMap<String, Object>();
                        String year = String.valueOf(hourReportRs.getString("YEAR"));
                        String month = String.valueOf(hourReportRs.getString("QUANTITY"));
                        String paymentTypeName = String.valueOf(hourReportRs.getString("PAYMENT_TYPE_NAME"));
                        BigDecimal salesAmount = hourReportRs.getBigDecimal("QUANTITY");
                        BigDecimal lastMonthSalesAmount = hourReportRs.getBigDecimal("LAST_MONTH_SALES_AMOUNT") == null ? new BigDecimal(0) : hourReportRs.getBigDecimal("LAST_MONTH_SALES_AMOUNT");
                        if (reportList.size() < 1) {
                            info.put("year", year);
                            info.put("month", month);
                            info.put("paymentTypeName", paymentTypeName);
                            info.put("salesAmount", salesAmount);
                            info.put("lastMonthSalesAmount", lastMonthSalesAmount);
                            reportList.add(info);
                        } else {
                            //如果有年、月、支付方式相同的，合并销售额
                            boolean flag = true;
                            for (int j = 0; j < reportList.size(); j++) {
                                LinkedHashMap<String, Object> reportMap = (LinkedHashMap<String, Object>) reportList.get(j);
                                if (year.equals(reportMap.get("year")) && month.equals(reportMap.get("month")) && paymentTypeName.equals(reportMap.get("paymentTypeName"))) {
                                    BigDecimal lastMonthSalesAmountForAdd = (BigDecimal) reportMap.get("lastMonthSalesAmount");
                                    if (lastMonthSalesAmountForAdd == null) {
                                        lastMonthSalesAmountForAdd = new BigDecimal(0);
                                    }
                                    flag = false;
                                    reportMap.put("salesAmount", salesAmount.add((BigDecimal) reportMap.get("salesAmount")));
                                    reportMap.put("lastMonthSalesAmountForAdd", lastMonthSalesAmount.add(lastMonthSalesAmountForAdd));
                                }
                            }
                            //年月小时均不同，添加进集合中成为新的元素
                            if (flag) {
                                info.put("year", year);
                                info.put("month", month);
                                info.put("paymentTypeName", paymentTypeName);
                                info.put("salesAmount", salesAmount);
                                info.put("lastMonthSalesAmount", lastMonthSalesAmount);
                                reportList.add(info);
                            }
                        }
                    }
                    hourReportRs.close();
                }
            }
            //按照枚举类放置数据
            for (int i = 0; i < reportList.size(); i++) {
                LinkedHashMap<String, Object> reportMap = (LinkedHashMap<String, Object>) reportList.get(i);
                LinkedHashMap<String, Object> row = new LinkedHashMap<String, Object>();
                row.put(FIELD_NAME.YEAR.toString(), reportMap.get("year"));
                row.put(FIELD_NAME.MONTH.toString(), reportMap.get("month"));
                row.put(FIELD_NAME.PAYMENT_TYPE_NAME.toString(), reportMap.get("paymentTypeName"));
                row.put(FIELD_NAME.SALES_AMOUNT.toString(), reportMap.get("salesAmount"));
                row.put(FIELD_NAME.LAST_MONTH_SALES_AMOUNT.toString(), reportMap.get("lastMonthSalesAmount"));
                rowData.add(row);
            }
            long end = System.currentTimeMillis();
            System.out.println("耗时：" + String.valueOf((end - start) / 1000) + "s");
            System.out.println("===================================s");
            // 释放数据库资源
            stmt.close();
            conn.close();

        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    // 释放一些资源，因为可能会有重复调用，所以需释放valueList，将上次查询的结果释放掉
    @Override
    public void release() throws Exception {
        super.release();
        this.rowData = null;
    }
}
